Connected to Snowflake.
Customer: KIVA
Schema: KIVA_PROD.OPTIML
2023-02-10 2023-02-18

Total cost breakdown¶

Cost by usage category¶

Category: Credit and dollar consumption trends
----------------------------------------------
category_name      credits_previous_week    dollars_previous_week    credits    dollars    pct_change_dollars
---------------  -----------------------  -----------------------  ---------  ---------  --------------------
Cloud services                     43.71                    87.42      43.15      86.3                  -1.28
Compute                           768.67                  1537.34     767.1     1534.19                 -0.2
Storage                             0                       60.96       0         65.76                  7.87
Total                             812.38                  1685.72     810.24    1686.25                  0.03

Cost by user¶

Users: Credit consumption trends
--------------------------------
user_name                  approximate_credits_previous_week    approximate_credits    pct_change_credits
-----------------------  -----------------------------------  ---------------------  --------------------
AMPLITUDE                                               0.27                   0.07                -74.07
DBT_DEV                                                72.58                  65.35                 -9.96
DBT_PROD                                              127.37                 130.38                  2.36
FIVETRAN_USER                                         339.12                 354.74                  4.61
FIVETRAN_USER_DEV                                      54.72                  56.12                  2.56
GREGORYW                                                1.19                   0.26                -78.15
GREGORYW_DEV                                            0.17                   1.7                 900
JAY                                                     0.01                   0                  -100
LOOKER_DEV_ADMIN                                        3.51                   3.36                 -4.27
LOOKER_PROD                                            34.38                  30.77                -10.5
LOOKER_PROD_ADMIN                                      18.57                  13.63                -26.6
LOOKER_RAW_DEV                                          0.37                   0.38                  2.7
LOOKER_RAW_PROD                                         4.05                   3.85                 -4.94
MAXH_DEV                                                0.01                   0.01                  0
ML_SERVICE_DEV                                         56.63                  53.29                 -5.9
ML_SERVICE_PROD                                        44.79                  42.02                 -6.18
OPTIML                                                  0.3                    0.65                116.67
ROBS                                                    0                      0.03                inf
VERTEX_API_DEV                                          1.31                   1.37                  4.58
VERTEX_API_DEV_JENKINS                                  0.39                   0.24                -38.46
VERTEX_API_PROD                                        52.62                  51.4                  -2.32
ANALYTICS_EXERCISE_USER                                 0                      0                   nan
HUGOV_DEV                                               0                      0.59                inf
Total                                                 812.36                 810.21                 -0.26
List of low usage users (<1% of credits) with usage (Current month)
-------------------------------------------------------------------
user_name                  approximate_credits    percent_usage
-----------------------  ---------------------  ---------------
AMPLITUDE                                 0.07            0.009
GREGORYW                                  0.26            0.032
GREGORYW_DEV                              1.7             0.21
JAY                                       0               0
LOOKER_DEV_ADMIN                          3.36            0.415
LOOKER_RAW_DEV                            0.38            0.047
LOOKER_RAW_PROD                           3.85            0.475
MAXH_DEV                                  0.01            0.001
OPTIML                                    0.65            0.08
ROBS                                      0.03            0.004
VERTEX_API_DEV                            1.37            0.169
VERTEX_API_DEV_JENKINS                    0.24            0.03
ANALYTICS_EXERCISE_USER                   0               0
HUGOV_DEV                                 0.59            0.073

Cost by warehouse¶

Warehouses: Credit consumption trends
-------------------------------------
warehouse_name         credits_previous_week    credits    pct_change_credits
-------------------  -----------------------  ---------  --------------------
CLOUD_SERVICES_ONLY                     0          0                     0
DEV_WH                                190.98     182.61                 -4.38
ML_WH                                  35.59      33.59                 -5.62
PROD_AUX_WH                            67.84      70.87                  4.47
PROD_WH                               517.97     523.17                  1
Total                                 810.24     812.38                  0.26

Cost by Partner Tools¶

Client Application: Credit consumption trends
---------------------------------------------
client_application_name      approximate_credits_previous_week    approximate_credits    pct_change_credits
-------------------------  -----------------------------------  ---------------------  --------------------
Go                                                        1.2                    0.39                -67.5
JDBC                                                    454.98                 462.97                  1.76
ODBC                                                     54.33                  53.01                 -2.43
Python                                                  301.86                 293.87                 -2.65
Snowflake UI                                              0.02                   0.01                -50
Total                                                   812.39                 810.25                 -0.26

Further Possible Actions on this notebook¶

  • Cost by tasks if that exists - doesnt seem to for Kiva - would like to understand this usage pattern
  • Cost by partner tools takes ~3 min for each run for 1 week. Needs to run twice during an analysis. Does this require mitigation? All other queries run in a few sec. each.
  • Provide recommendation for total resource monitor - weekly
  • Provide recommendation for resource monitor by day of the week:
    • Warehouse
    • Overall
  • Next set of notebooks

Resource monitoring¶

Runbook¶

  • If the resource monitor did not send a notification last week and the new resource monitor proposed is within 10% of previous monitor - dont update the resource monitor

  • If the resource monitor sent a notification last week due to an unexplained usage that cannot be attributed to legitimate use continue to debug and dont change the resource monitor

  • If the resource monitor sent a notification last week that can be attributed to legitimate use update the resource monitor

  • If the new resource monitor values are >10% over last week's values update the resource monitor

Query templates for you to generate resource monitor for this week

USE ROLE ACCOUNTADMIN;
CREATE OR REPLACE RESOURCE MONITOR DEV_WH_RESOURCE_MONITOR 
WITH CREDIT_QUOTA=31.28
FREQUENCY=DAILY
START_TIMESTAMP=YYYY-MM-DD HH:MM:SS PST
TRIGGERS ON 100 PERCENT DO NOTIFY;
ALTER WAREHOUSE DEV_WH SET RESOURCE_MONITOR=DEV_WH_RESOURCE_MONITOR;


USE ROLE ACCOUNTADMIN;
CREATE OR REPLACE RESOURCE MONITOR ML_WH_RESOURCE_MONITOR 
WITH CREDIT_QUOTA=6.28
FREQUENCY=DAILY
START_TIMESTAMP=YYYY-MM-DD HH:MM:SS PST
TRIGGERS ON 100 PERCENT DO NOTIFY;
ALTER WAREHOUSE ML_WH SET RESOURCE_MONITOR=ML_WH_RESOURCE_MONITOR;


USE ROLE ACCOUNTADMIN;
CREATE OR REPLACE RESOURCE MONITOR PROD_AUX_WH_RESOURCE_MONITOR 
WITH CREDIT_QUOTA=9.2
FREQUENCY=DAILY
START_TIMESTAMP=YYYY-MM-DD HH:MM:SS PST
TRIGGERS ON 100 PERCENT DO NOTIFY;
ALTER WAREHOUSE PROD_AUX_WH SET RESOURCE_MONITOR=PROD_AUX_WH_RESOURCE_MONITOR;


USE ROLE ACCOUNTADMIN;
CREATE OR REPLACE RESOURCE MONITOR PROD_WH_RESOURCE_MONITOR 
WITH CREDIT_QUOTA=61.24
FREQUENCY=DAILY
START_TIMESTAMP=YYYY-MM-DD HH:MM:SS PST
TRIGGERS ON 100 PERCENT DO NOTIFY;
ALTER WAREHOUSE PROD_WH SET RESOURCE_MONITOR=PROD_WH_RESOURCE_MONITOR;

Backtesting resource monitor based on data from 2022-10-05 to 2022-10-12¶

DEV_WH
ML_WH
PROD_AUX_WH
PROD_WH

Checking against warehouse timeseries data for credit usage¶

Backtesting resource monitor based on data from 2022-09-27 to 2022-10-04¶

DEV_WH
ML_WH
PROD_AUX_WH
PROD_WH